package com.authine.cloudpivot.ext.utils;

import com.authine.cloudpivot.ext.dto.VendorGoodDto;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class VendorGoodJdbcUtil {



    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);

    public static final String VENDOR_GOOD_SQL = "SELECT\n" +
            "\tspwl.id,\n" +
            "\tGROUP_CONCAT( ptkh.member_name ) member_name,\n" +
            "\tspwl.vendor_id,\n" +
            "\tspwl.org_fullname,\n" +
            "\tspwl.catalog,\n" +
            "\tspwl.brand,\n" +
            "\tspwl.name,\n" +
            "\tspwl.model,\n" +
            "\tspwl.spec,\n" +
            "\tspwl.unit,\n" +
            "\tspwl.package_num,\n" +
            "\tspwl.package_unit,\n" +
            "\tspwl.delivery,\n" +
            "\tspwl.min_order,\n" +
            "\tspwl.pretax_price,\n" +
            "\tspwl.tax_rate,\n" +
            "\tspwl.stock,\n" +
            "\tspwl.expiry,\n" +
            "\tspwl.createdTime,\n" +
            "\tspwl.saled,\n" +
            "\tspwl.enabled \n" +
            "FROM\n" +
            "\tcommoditymaterials.i_commoditymaterials_t_goodses spwl\n" +
            "LEFT JOIN operation1center.i_operation1center_t_platform_grant_user ptkh ON ptkh.organization_id = spwl.organization_id  and ptkh.deleted=0\n" +
            "WHERE 1=1 AND  spwl.deleted=0 \n";
    public static Integer getCountSql (VendorGoodDto good){
        StringBuffer sql = new StringBuffer();
        sql.append(VENDOR_GOOD_SQL);
        Map<String, Object> parameter = new HashMap<>();
        getCommonConditionSql(good, sql,parameter);
        getGroupSql(sql);
        log.info("sql:{}",sql);
        return JdbcTemplateUtils.queryForList(sql.toString(),parameter).size();
    }

    public static List<Map<String,Object>> getVendorGoodList (VendorGoodDto good,Integer limit ,Integer size){
        StringBuffer sql = new StringBuffer();
        sql.append(VENDOR_GOOD_SQL);
        Map<String, Object> parameter = new HashMap<>();
        getCommonConditionSql(good, sql,parameter);

        sql = getGroupSql(sql);
        if (limit != null && size != null){
            sql.append( " limit "+limit +","+size);
        }
        List<Map<String, Object>> maps = JdbcTemplateUtils.queryForList(sql.toString(),parameter);
        return maps;
    }

    private static void getCommonConditionSql(VendorGoodDto good, StringBuffer whereSql,Map<String,Object> parameter ) {

        if (StringUtils.isNotBlank(good.getType())){
            List<String> types = StringUtils.stringToListString(good.getType(), ";");
            whereSql.append(" and spwl.type in (:types) ");
            parameter.put("types", types);
        }

        if (org.apache.commons.lang3.StringUtils.isNotEmpty(good.getBrand())){
            whereSql.append(" and spwl.brand like :brand ");
            parameter.put("brand",Utils.likeVal(good.getBrand()));
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(good.getName())){
            whereSql.append(" and spwl.name like :name ");
            parameter.put("name", Utils.likeVal(good.getName()));
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(good.getModel())){
            whereSql.append(" and spwl.model like :model ");
            parameter.put("model", Utils.likeVal(good.getModel()));
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(good.getSpec())){
            whereSql.append(" and spwl.spec like :spec ");
            parameter.put("spec", Utils.likeVal(good.getSpec()));
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(good.getPriceState())){
            if (org.apache.commons.lang3.StringUtils.equals("0",good.getPriceState())){
                whereSql.append(" and spwl.expiry <= now()");
            }
            if (org.apache.commons.lang3.StringUtils.equals("1",good.getPriceState())){
                whereSql.append(" and spwl.expiry >= now()");
            }
        }
        if (good.getStartTime() != null){
            whereSql.append(" and spwl.createdTime >=:startTime");
            parameter.put("startTime", good.getStartTime());
        }
        if (good.getEndTime() != null){
            whereSql.append(" and spwl.createdTime <=:endTime");
            parameter.put("endTime", good.getEndTime());
        }
    }

    public static StringBuffer getGroupSql(StringBuffer sql){
        sql.append ( "\t GROUP BY spwl.id");
        return  sql;
    }
}
